package com.tpc.server.modules.core.contact;

/**
 * @version 0.0.1
 * @author frederic.fortin
 * @modified 2010-08-17
 */

import java.sql.ResultSet;
import java.util.logging.Logger;
import com.google.gwt.user.server.rpc.RemoteServiceServlet;
import com.tpc.client.modules.core.contact.service.ContactService;
import com.tpc.dao.extend.Contact;
import com.tpc.dao.extend.Phone;
import com.tpc.dao.extend.Tag;
import com.tpc.dao.extend.Url;
import com.tpc.server.systeme.utils.DateManager;
import com.tpc.server.systeme.utils.SecuriteManager;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Locale;


@SuppressWarnings("serial")
public class ContactServiceImpl extends RemoteServiceServlet implements ContactService {

    private static final long serialVersionUID = 3L;
    private final static Logger objLog = Logger.getLogger(ContactServiceImpl.class.getName());

    /** @PROJECTSPECIFIC  Modify if YOUR PROJECT needs it**/
   String strQueryLoad = "SELECT user.created_date,user.modified_date,user_contact.userId,user_key_title.tagkeyId AS TITLEID,user.firstname,user.lastname,user.birthdate,user.description,"
           + "ORG.id AS ORGID,ORG.name AS ORGNAME,user_key_function.tagkeyId AS JOBID,user_key_language.tagkeyId AS LANGUEID,address.street AS ORGSTREET,address.suite AS ORGSUITE,"
           + "address_zipcode.zipcode AS ORGZIPCODE,address_city.name AS ORGCITY,address_state.name AS ORGSTATE,address_country.name AS ORGCOUNTRY,"
           + "XUC.function_since, XUC.hot_issue, XUC.strategy FROM organisation ORG JOIN organisation_has_user ORGCTC ON "
           + "ORG.id = ORGCTC.organisationId JOIN user_contact ON ORGCTC.userId = user_contact.userId JOIN user ON user_contact.userId = user.id LEFT JOIN user_key_title ON user.id = user_key_title.userId "
           + "LEFT JOIN user_key_function ON user.id = user_key_function.userId LEFT JOIN user_key_language ON user.id = user_key_language.userId LEFT JOIN xtend_user_contact XUC ON "
           + "XUC.userId = user_contact.userId LEFT JOIN organisation_has_address ORGADD ON ORG.id = ORGADD.organisationId  LEFT JOIN address ON ORGADD.addressId = address.id AND address.defaut = ? "
           + "AND address.life = ? AND address.TYPE = ? LEFT JOIN address_zipcode ON address_zipcode.id = address.zipcodeId LEFT JOIN address_city ON address_city.id = address_zipcode.addressCityId "
           + "LEFT JOIN address_state ON address_city.addressStateId = address_state.id LEFT JOIN address_country ON address_state.countryId = address_country.id WHERE user.id = ?";

    /**  Modify if YOUR PROJECT needs it**/
    
    public ContactServiceImpl() {

    }
    
    /** LoadListe **/
    public Contact[] loadList() {
        try {
            Contact[] arrobjContact = null;
            SecuriteManager objSecurite = new SecuriteManager(this.getThreadLocalRequest());
            if (objSecurite.isAccessAllowed(Contact.class.getSimpleName(),"READ")) {
                String sqlQuery = "SELECT user.id AS UID,user.firstname,user.lastname,user.description,organisation.name,(SELECT url.url FROM url,user_has_url WHERE url.id = user_has_url.urlId AND user_has_url.userId = UID LIMIT 0,1) EMAIL,"
                        + "(SELECT phone_areacode.areacode FROM phone,phone_areacode,user_has_phone WHERE user_has_phone.phoneId = phone.id AND phone.phoneAreacodeId = phone_areacode.id AND phone.life = 1 AND "
                        + "phone.defaut = 1 AND phone.TYPE = 'BUSINESS' AND user_has_phone.userId = UID Limit 0,1) AS AREACODE,(SELECT phone.number FROM phone,user_has_phone WHERE user_has_phone.phoneId = phone.id "
                        + "AND phone.life = 1 AND phone.defaut = 1 AND phone.TYPE = 'BUSINESS' AND user_has_phone.userId = UID Limit 0,1) AS PHONE,(SELECT phone_areacode.areacode FROM phone,phone_areacode,user_has_phone "
                        + "WHERE user_has_phone.phoneId = phone.id AND phone.phoneAreacodeId = phone_areacode.id AND phone.life = 1 AND phone.defaut = 1 AND phone.TYPE = 'MOBILE' AND user_has_phone.userId = UID Limit 0,1) "
                        + "AS AREACODE_CELL,(SELECT phone.number FROM phone,user_has_phone WHERE user_has_phone.phoneId = phone.id AND phone.life = 1 AND phone.defaut = 1 AND phone.TYPE = 'MOBILE' AND "
                        + "user_has_phone.userId = UID Limit 0,1) AS PHONE_CELL,(SELECT tag_key.name FROM tag_key,user_key_title WHERE user_key_title.tagkeyId = tag_key.id AND user_key_title.userId = UID LIMIT 0,1) TITRE "
                        + "FROM user,organisation,organisation_has_user WHERE organisation_has_user.userId = user.id AND organisation_has_user.organisationId = organisation.id AND user.life = 1";
                if (!new Boolean(objSecurite.getUser().getIsAdmin())) {
                    sqlQuery += " AND organisation.id = "+objSecurite.getUser().getOrganisation().getId();
                }
                String[] arrstrParameters = null;
                ResultSet objRS = objSecurite.getDataBaseManager().executeQuery(sqlQuery, arrstrParameters);
                if (objRS != null) {
                    int i = 0;
                    int intNbrRow = 0;
                    objRS.last();
                    intNbrRow = objRS.getRow();
                    arrobjContact = new Contact[intNbrRow];
                    objRS.beforeFirst();
                    while (objRS.next()) {
                        /** @PROJECTSPECIFIC  Modify if YOUR PROJECT needs it**/
                        arrobjContact[i] = new Contact();
                        arrobjContact[i].setId(objRS.getInt("UID"));
                        arrobjContact[i].setFirstName(objRS.getString("user.firstname"));
                        arrobjContact[i].setLastName(objRS.getString("user.lastname"));
                        arrobjContact[i].setDescription(objRS.getString("user.description"));
                        arrobjContact[i].setOrganisation(-1,objRS.getString("organisation.name"));
                        arrobjContact[i].setEmail(-1,objRS.getString("EMAIL"));
                        arrobjContact[i].setPhoneBusiness(-1,objRS.getString("AREACODE"),objRS.getString("PHONE"),null);
                        arrobjContact[i].setPhoneMobile(-1,objRS.getString("AREACODE_CELL"),objRS.getString("PHONE_CELL"));
                        arrobjContact[i].setTitle(objRS.getString("TITRE"));
                        /** Modify if YOUR PROJECT needs it**/
                        i++;
                    }
                }
                objSecurite.getDataBaseManager().closeConnection();
            }
            return arrobjContact;
        }
        catch (Exception objx) {
            objLog.info("message 2: "+objx);
            return null;
        }
    }

    /** LoadListe **/
    public Contact[] loadListContact(String strOrganisationId) {
        try {
            Contact[] arrobjContact = null;
            SecuriteManager objSecurite = new SecuriteManager(this.getThreadLocalRequest());
            if (objSecurite.isAccessAllowed(Contact.class.getSimpleName(),"READ")) {
                String sqlLoadListContact = "SELECT user.id AS UID,user.firstname,user.lastname,user.description,functionTable.name AS JOB,"
                        + "(SELECT username FROM identity,user_has_identity WHERE user_has_identity.userId = UID AND user_has_identity.identityId = identity.id AND identity.life =1 AND identity.name = 'serpvaccin_login' LIMIT 0,1) AS USERNAME,"
                        + "(SELECT password FROM identity,user_has_identity WHERE user_has_identity.userId = UID AND user_has_identity.identityId = identity.id AND identity.life =1 AND identity.name = 'serpvaccin_login' LIMIT 0,1) AS PASSWORD,"
                        + "(SELECT phone_areacode.areacode FROM phone,user_has_phone,phone_areacode WHERE user_has_phone.userId = UID AND user_has_phone.phoneId = phone.id AND "
                        + "phone.defaut = ? AND phone.life = ? AND phone.TYPE = ? AND phone.phoneAreacodeId = phone_areacode.id LIMIT 0,1) AS PHONE_B_AREA,(SELECT phone.number FROM phone,user_has_phone "
                        + "WHERE user_has_phone.userId = UID AND user_has_phone.phoneId = phone.id AND phone.defaut = ? AND phone.life = ? AND phone.TYPE = ? LIMIT 0,1) AS PHONE_B,"
                        + "(SELECT phone_areacode.areacode FROM phone,user_has_phone,phone_areacode WHERE user_has_phone.userId = UID AND user_has_phone.phoneId = phone.id AND "
                        + "phone.defaut = ? AND phone.life = ? AND phone.TYPE = ? AND phone.phoneAreacodeId = phone_areacode.id LIMIT 0,1) AS CELLAREA,(SELECT phone.number FROM phone,user_has_phone "
                        + "WHERE user_has_phone.userId = UID AND user_has_phone.phoneId = phone.id AND phone.defaut = ? AND phone.life = ? AND phone.TYPE = ? LIMIT 0,1) AS CELL, "
                        + " (SELECT url FROM url,user_has_url WHERE url.life = ? AND url.defaut = ? AND url.TYPE = ? AND url.id = user_has_url.urlId AND user_has_url.userId = UID LIMIT 0,1) AS EMAIL,"
                        + " (SELECT tag_key.name FROM tag_key,user_key_title WHERE tag_key.id = user_key_title.tagkeyId AND user_key_title.userId = UID) AS TITLE  FROM organisation ORG "
                        + "JOIN organisation_has_user ORGCTC ON ORG.id = ORGCTC.organisationId JOIN user_contact ON ORGCTC.userId = user_contact.userId JOIN user ON user_contact.userId = "
                        + "user.id LEFT JOIN user_key_title ON user.id = user_key_title.userId LEFT JOIN tag_key titleTable ON user_key_title.tagkeyId = titleTable.id LEFT JOIN user_key_function "
                        + "ON user.id = user_key_function.userId LEFT JOIN tag_key functionTable ON user_key_function.tagkeyId = functionTable.id WHERE ORG.id = ? GROUP BY user.id";
                String[] Parameters = {objSecurite.getProperties().getProperty("DEFAULT"),objSecurite.getProperties().getProperty("ENABLE"),objSecurite.getProperties().getProperty("TYPE_PHONE_BUSINESS"),objSecurite.getProperties().getProperty("DEFAULT"),objSecurite.getProperties().getProperty("ENABLE"),objSecurite.getProperties().getProperty("TYPE_PHONE_BUSINESS"),objSecurite.getProperties().getProperty("DEFAULT"),objSecurite.getProperties().getProperty("ENABLE"),objSecurite.getProperties().getProperty("TYPE_PHONE_MOBILE"),objSecurite.getProperties().getProperty("DEFAULT"),objSecurite.getProperties().getProperty("ENABLE"),objSecurite.getProperties().getProperty("TYPE_PHONE_MOBILE"),objSecurite.getProperties().getProperty("ENABLE"),objSecurite.getProperties().getProperty("DEFAULT"),objSecurite.getProperties().getProperty("TYPE_EMAIL"),strOrganisationId};
                ResultSet objRS = objSecurite.getDataBaseManager().executeQuery(sqlLoadListContact,Parameters);
                if (objRS != null) {
                    int i = 0;
                    int intNbrRow = 0;
                    objRS.last();
                    intNbrRow = objRS.getRow();
                    arrobjContact = new Contact[intNbrRow];
                    objRS.beforeFirst();
                    while (objRS.next()) {
                        /** @PROJECTSPECIFIC  Modify if YOUR PROJECT needs it**/
                        arrobjContact[i] = new Contact();
                        arrobjContact[i].setId(objRS.getInt("UID"));
                        arrobjContact[i].setTitle(objRS.getString("TITLE"));
                        arrobjContact[i].setFirstName(objRS.getString("user.firstname"));
                        arrobjContact[i].setLastName(objRS.getString("user.lastname"));
                        arrobjContact[i].setUsername(objRS.getString("USERNAME"));
                        arrobjContact[i].setPassword(objRS.getString("PASSWORD"));
                        arrobjContact[i].setDescription(objRS.getString("user.description"));
                        arrobjContact[i].setEmail(-1,objRS.getString("EMAIL"));
                        arrobjContact[i].setPhoneBusiness(-1,objRS.getString("PHONE_B_AREA"),objRS.getString("PHONE_B"),null);
                        arrobjContact[i].setPhoneMobile(-1,objRS.getString("CELLAREA"),objRS.getString("CELL"));
                        arrobjContact[i].setFunction(objRS.getString("JOB"));
                        /** Modify if YOUR PROJECT needs it**/
                        i++;
                    }
                }
                objSecurite.getDataBaseManager().closeConnection();
            }
            return arrobjContact;
        }
        catch (Exception objx) {
            objLog.info("message 2: "+objx);
            return null;
        }
    }

    public Contact[] loadListContactTask(String strOrganisationId,String contactTerm) {
        try {
            Contact[] ListeContact = null;
            SecuriteManager objSecurite = new SecuriteManager(this.getThreadLocalRequest());
            if (objSecurite.isAccessAllowed(Contact.class.getSimpleName(),"READ")) {
                String sqlLoadListContact = "SELECT user.id,user.firstname,user.lastname FROM user,organisation_has_user,user_contact,organisation WHERE ";
                sqlLoadListContact += "organisation.id = organisation_has_user.organisationId AND organisation_has_user.userId = user_contact.userId AND ";
                sqlLoadListContact += "user_contact.userId = user.id AND ";
                if (strOrganisationId != null) {
                    sqlLoadListContact += "organisation.id = "+strOrganisationId+" AND ";
                }
                sqlLoadListContact += " (firstname LIKE '%"+contactTerm+"%' OR lastname LIKE '%"+contactTerm+"%') LIMIT 0,10";
                String[] Parameters = null;
                ResultSet objRS = objSecurite.getDataBaseManager().executeQuery(sqlLoadListContact,Parameters);
                if (objRS != null) {
                    int i = 0;
                    int intNbrRow = 0;
                    objRS.last();
                    intNbrRow = objRS.getRow();
                    ListeContact = new Contact[intNbrRow];
                    objRS.beforeFirst();
                    while (objRS.next()) {
                        /** @PROJECTSPECIFIC  Modify if YOUR PROJECT needs it**/
                        ListeContact[i] = new Contact();
                        ListeContact[i].setId(objRS.getInt("user.id"));
                        ListeContact[i].setFirstName(objRS.getString("user.firstname"));
                        ListeContact[i].setLastName(objRS.getString("user.lastname"));
                        /** Modify if YOUR PROJECT needs it**/
                        i++;
                    }
                }
                objSecurite.getDataBaseManager().closeConnection();
            }
            return ListeContact;
        }
        catch (Exception objx) {
            objLog.info("message 2: "+objx);
            return null;
        }
    }

    /** **/
    public Tag[] loadListTitle() {
        try {
            SecuriteManager objSecurite = new SecuriteManager(this.getThreadLocalRequest());
            Tag[] Titles = null;
            String sqlTitle = "SELECT id,name FROM tag_key,taggroup_has_key WHERE taggroup_has_key.taggroupId = ? AND taggroup_has_key.tagkeyId = tag_key.id AND tag_key.life = ? ORDER BY id";
            String[] parameters = {"4","1"};
            ResultSet objRS = objSecurite.getDataBaseManager().executeQuery(sqlTitle, parameters);
            if (objRS != null) {
                int i = 0;
                int intNbrRow = 0;
                objRS.last();
                intNbrRow = objRS.getRow();
                Titles = new Tag[intNbrRow];
                objRS.beforeFirst();
                while (objRS.next()) {
                    Titles[i] = new Tag();
                    Titles[i].setId(objRS.getInt("id"));
                    Titles[i].setName(objRS.getString("name"));
                    i++;
                }
            }
            objSecurite.getDataBaseManager().closeConnection();
            return Titles;
        }
        catch (Exception objX) {
            objLog.info("message 2: "+objX.getMessage());
            return null;
        }
    }

    public Tag[] loadListGroupe() {
        try {
            SecuriteManager objSecurite = new SecuriteManager(this.getThreadLocalRequest());
            Tag[] groupes = null;
            String sqlTitle = "SELECT id,name FROM groupe ORDER BY name";
            String[] parameters = null;
            ResultSet objRS = objSecurite.getDataBaseManager().executeQuery(sqlTitle, parameters);
            if (objRS != null) {
                int i = 0;
                int intNbrRow = 0;
                objRS.last();
                intNbrRow = objRS.getRow();
                groupes = new Tag[intNbrRow];
                objRS.beforeFirst();
                while (objRS.next()) {
                    groupes[i] = new Tag();
                    groupes[i].setId(objRS.getInt("id"));
                    groupes[i].setName(objRS.getString("name"));
                    i++;
                }
            }
            objSecurite.getDataBaseManager().closeConnection();
            return groupes;
        }
        catch (Exception objX) {
            objLog.info("message 2: "+objX.getMessage());
            return null;
        }
    }

    public Tag[] loadListFuntion() {
        try {
            SecuriteManager objSecurite = new SecuriteManager(this.getThreadLocalRequest());
            Tag[] Jobs = null;
            String sqlTitle = "SELECT id,name FROM tag_key,taggroup_has_key WHERE taggroup_has_key.taggroupId = ? AND taggroup_has_key.tagkeyId = tag_key.id AND tag_key.life = ? ORDER BY id";
            String[] parameters = {"5","1"};
            ResultSet objRS = objSecurite.getDataBaseManager().executeQuery(sqlTitle, parameters);
            if (objRS != null) {
                int i = 0;
                int intNbrRow = 0;
                objRS.last();
                intNbrRow = objRS.getRow();
                Jobs = new Tag[intNbrRow];
                objRS.beforeFirst();
                while (objRS.next()) {
                    Jobs[i] = new Tag();
                    Jobs[i].setId(objRS.getInt("id"));
                    Jobs[i].setName(objRS.getString("name"));
                    i++;
                }
            }
            objSecurite.getDataBaseManager().closeConnection();
            return Jobs;
        }
        catch (Exception objX) {
            objLog.info("message 2: "+objX.getMessage());
            return null;
        }
    }

    public Tag[] loadListLangue() {
        try {
            SecuriteManager objSecurite = new SecuriteManager(this.getThreadLocalRequest());
            Tag[] Languages = null;
            String sqlTitle = "SELECT id,name FROM tag_key,taggroup_has_key WHERE taggroup_has_key.taggroupId = ? AND taggroup_has_key.tagkeyId = tag_key.id AND tag_key.life = ? ORDER BY id";
            String[] parameters = {"8","1"};
            ResultSet objRS = objSecurite.getDataBaseManager().executeQuery(sqlTitle, parameters);
            if (objRS != null) {
                int i = 0;
                int intNbrRow = 0;
                objRS.last();
                intNbrRow = objRS.getRow();
                Languages = new Tag[intNbrRow];
                objRS.beforeFirst();
                while (objRS.next()) {
                    Languages[i] = new Tag();
                    Languages[i].setId(objRS.getInt("id"));
                    Languages[i].setName(objRS.getString("name"));
                    i++;
                }
            }
            objSecurite.getDataBaseManager().closeConnection();
            return Languages;
        }
        catch (Exception objX) {
            objLog.info("message 2: "+objX.getMessage());
            return null;
        }
    }

    /** Load One **/
    public Contact load(String strId) {
        try {
            Contact objContact = null;
            SecuriteManager objSecurite = new SecuriteManager(this.getThreadLocalRequest());
            if (objSecurite.isAccessAllowed(Contact.class.getSimpleName(),"READ")) {
                String sqlQuery = "SELECT user.id AS UID,user.firstname,user.lastname,user.description,user.birthdate,user.created_date,user.modified_date,organisation.id,organisation.name,"
                        + "(SELECT tag_key.id FROM tag_key,user_key_title WHERE user_key_title.tagkeyId = tag_key.id AND user_key_title.userId = UID LIMIT 0,1) TITRE,"
                        + "(SELECT tag_key.id FROM tag_key,user_key_language WHERE user_key_language.tagkeyId = tag_key.id AND user_key_language.userId = UID LIMIT 0,1) LANGUE FROM user,organisation,"
                        + "organisation_has_user WHERE user.id = organisation_has_user.userId AND organisation_has_user.organisationId = organisation.id AND user.id = ?";
                String[] arrstrParameters = {strId};
                ResultSet objRS = objSecurite.getDataBaseManager().executeQuery(sqlQuery, arrstrParameters);
                if (objRS != null) {
                    if (objRS.next()) {
                        objContact = new Contact();
                        objContact.setId(objRS.getInt("UID"));
                        objContact.setLastName(objRS.getString("user.lastname"));
                        objContact.setFirstName(objRS.getString("user.firstname"));
                        objContact.setTitle(objRS.getInt("TITRE"));
                        objContact.setOrganisation(objRS.getInt("organisation.id"),objRS.getString("organisation.name"));
                        objContact.setLangue(objRS.getInt("LANGUE"));
                        objContact.setBirthDate(objRS.getDate("user.birthdate"));
                        objContact.setDescription(objRS.getString("user.description"));
                        SimpleDateFormat sdfDDmmYYYY = new SimpleDateFormat(objSecurite.getProperties().getProperty("DATE_FORMAT_VIEW"),Locale.US);
                        StringBuilder stbDateCreated = new StringBuilder("");
                        if (objRS.getDate("user.created_date") != null) {
                            stbDateCreated = new StringBuilder(sdfDDmmYYYY.format(objRS.getDate("user.created_date")));
                        }
                        objContact.setCreatedDateAsString(stbDateCreated.toString().toUpperCase());
                        StringBuilder stbDateModified = new StringBuilder("");
                        if (objRS.getDate("user.modified_date") != null) {
                            stbDateModified = new StringBuilder(sdfDDmmYYYY.format(objRS.getDate("user.modified_date")));
                        }
                        objContact.setModifiedDateAsString(stbDateModified.toString().toUpperCase());
                        if (objContact != null) {
                            /* IDENTITY : username / password*/
                            String sqlUP = "SELECT username,password FROM identity,user_has_identity WHERE user_has_identity.userId = ? AND user_has_identity.identityId = identity.id AND identity.name = ? AND life = ?";
                            String[] paramUP = {strId,objSecurite.getProperties().getProperty("IDENTITY_NAME_LOGIN"),objSecurite.getProperties().getProperty("ENABLE")};
                            ResultSet rsUP = objSecurite.getDataBaseManager().executeQuery(sqlUP,paramUP);
                            if (rsUP != null) {
                                if (rsUP.next()) {
                                    objContact.setUsername(rsUP.getString("username"));
                                    objContact.setPassword(rsUP.getString("password"));
                                }
                            }

                            String sqlContactPhone = "SELECT phone.id,phone_areacode.areacode,number,extension,phone.defaut,phone.TYPE FROM phone, phone_areacode,user_has_phone "
                                    + "WHERE user_has_phone.userId = ? AND user_has_phone.phoneId = phone.id AND phone.phoneAreacodeId = phone_areacode.id AND phone.life = ?";
                            String[] arrstrParamContactPhone = {strId,objSecurite.getProperties().getProperty("ENABLE")};
                            ResultSet objRSphone = objSecurite.getDataBaseManager().executeQuery(sqlContactPhone, arrstrParamContactPhone);
                            if (objRSphone != null) {
                                int i = 0;
                                objRSphone.last();
                                int intNbrRow = objRSphone.getRow();
                                Phone[] ContactPhones = new Phone[intNbrRow];
                                objRSphone.beforeFirst();
                                while(objRSphone.next()) {
                                    ContactPhones[i] = new Phone();
                                    ContactPhones[i].setId(objRSphone.getInt("phone.id"));
                                    ContactPhones[i].setAreacode(objRSphone.getString("phone_areacode.areacode"));
                                    ContactPhones[i].setNumber(objRSphone.getString("number"));
                                    ContactPhones[i].setExtension(objRSphone.getString("extension"));
                                    ContactPhones[i].setDefaut(objRSphone.getInt("phone.defaut"));
                                    ContactPhones[i].setType(objRSphone.getString("phone.TYPE"));
                                    i++;
                                }
                                objContact.setPhones(ContactPhones);
                            }
                            String sqlContactEmail = "SELECT url.id,url.url,url.defaut,url.TYPE FROM url, user_has_url WHERE user_has_url.urlId = url.id AND user_has_url.userId = ? AND url.life = ? AND TYPE = ?";
                            String[] arrstrParamContactEmail = {strId,objSecurite.getProperties().getProperty("ENABLE"),objSecurite.getProperties().getProperty("TYPE_EMAIL")};
                            ResultSet objRSemail = objSecurite.getDataBaseManager().executeQuery(sqlContactEmail, arrstrParamContactEmail);
                            if (objRSemail != null) {
                                int i = 0;
                                objRSemail.last();
                                int intNbrRow = objRSemail.getRow();
                                Url[] ContactEmails = new Url[intNbrRow];
                                objRSemail.beforeFirst();
                                while(objRSemail.next()) {
                                    ContactEmails[i] = new Url();
                                    ContactEmails[i].setId(objRSemail.getInt("url.id"));
                                    ContactEmails[i].setDefaut(objRSemail.getInt("url.defaut"));
                                    ContactEmails[i].setUrl(objRSemail.getString("url.url"));
                                    ContactEmails[i].setType(objRSemail.getString("url.TYPE"));
                                    i++;
                                }
                                objContact.setEmails(ContactEmails);
                            }
                            // HOME ADDRESS
                            String sqlHomeAddress = "SELECT address.id,address.street,address.suite,address_zipcode.id,address_zipcode.zipcode,address_city.id,address_city.name,"
                                    + " address_state.id,address_state.name,address_country.id,address_country.name FROM  address, address_city, address_country,address_state, address_zipcode,"
                                    + "user_has_address WHERE  user_has_address.addressId = address.id AND address.zipcodeId = address_zipcode.id AND address_zipcode.addressCityId = address_city.id "
                                    + "AND address_city.addressStateId = address_state.id AND address_state.countryId = address_country.id AND user_has_address.userId = ?";
                            String[] paramHomeAddress = {strId};
                            ResultSet rs = objSecurite.getDataBaseManager().executeQuery(sqlHomeAddress,paramHomeAddress);
                            if (rs != null) {
                                if (rs.next()) {
                                    objContact.setAddressHome(rs.getInt("address.id"),rs.getString("address.street"),rs.getString("address.suite"),rs.getInt("address_zipcode.id"),
                                            rs.getString("address_zipcode.zipcode"),rs.getInt("address_city.id"),rs.getString("address_city.name"),rs.getInt("address_state.id"),
                                            rs.getString("address_state.name"),rs.getInt("address_country.id"),rs.getString("address_country.name"));
                                }
                            }
                        }
                    }
                }
                objSecurite.getDataBaseManager().closeConnection();
            }
            return objContact;
        }
        catch(SQLException sqlEx) {
            objLog.info("message 1 : "+sqlEx.getMessage());
            return null;
        }
        catch (Exception objx) {
            objLog.info("message 2: "+objx.getMessage());
            return null;
        }
    }

    public Contact loadCoordonnee(String strId) {
        try {
            Contact contact = null;
            SecuriteManager objSecurite = new SecuriteManager(this.getThreadLocalRequest());
            if (objSecurite.isAccessAllowed(Contact.class.getSimpleName(),"READ")) {
                String sqlLoadContact = "SELECT id FROM user WHERE id = ?";
                String[] arrstrParameters = {strId};
                ResultSet objRS = objSecurite.getDataBaseManager().executeQuery(sqlLoadContact,arrstrParameters);
                if (objRS != null) {
                    if (objRS.next()) {
                        contact = new Contact();
                        contact.setId(objRS.getInt("id"));
                    }
                }
                if (contact != null) {
                    String sqlHotIssues = "SELECT hot_issue,strategy FROM xtend_user_contact WHERE userId = ?";
                    String[] parameterHI = {strId};
                    ResultSet rsHI = objSecurite.getDataBaseManager().executeQuery(sqlHotIssues, parameterHI);
                    if (rsHI != null) {
                        if (rsHI.next()) {
                            contact.setHotIssues(rsHI.getString("hot_issue"));
                            contact.setStrategy(rsHI.getString("strategy"));
                        }
                    }

                    String sqlContactPhone = "SELECT phone.id,phone_areacode.areacode,number,extension,phone.defaut,phone.TYPE FROM phone, phone_areacode,user_has_phone "
                            + "WHERE user_has_phone.userId = ? AND user_has_phone.phoneId = phone.id AND phone.phoneAreacodeId = phone_areacode.id AND phone.life = ?";
                    String[] arrstrParamContactPhone = {strId,objSecurite.getProperties().getProperty("ENABLE")};
                    ResultSet objRSphone = objSecurite.getDataBaseManager().executeQuery(sqlContactPhone, arrstrParamContactPhone);
                    if (objRSphone != null) {
                        int i = 0;
                        objRSphone.last();
                        int intNbrRow = objRSphone.getRow();
                        Phone[] ContactPhones = new Phone[intNbrRow];
                        objRSphone.beforeFirst();
                        while(objRSphone.next()) {
                            ContactPhones[i] = new Phone();
                            ContactPhones[i].setId(objRSphone.getInt("phone.id"));
                            ContactPhones[i].setAreacode(objRSphone.getString("phone_areacode.areacode"));
                            ContactPhones[i].setNumber(objRSphone.getString("number"));
                            ContactPhones[i].setExtension(objRSphone.getString("extension"));
                            ContactPhones[i].setDefaut(objRSphone.getInt("phone.defaut"));
                            ContactPhones[i].setType(objRSphone.getString("phone.TYPE"));
                            i++;
                        }
                        contact.setPhones(ContactPhones);
                    }
                    String sqlContactEmail = "SELECT url.id,url.url,url.defaut,url.TYPE FROM url, user_has_url WHERE user_has_url.urlId = url.id AND user_has_url.userId = ? AND url.life = ? AND TYPE = ?";
                    String[] arrstrParamContactEmail = {strId,objSecurite.getProperties().getProperty("ENABLE"),objSecurite.getProperties().getProperty("TYPE_EMAIL")};
                    ResultSet objRSemail = objSecurite.getDataBaseManager().executeQuery(sqlContactEmail, arrstrParamContactEmail);
                    if (objRSemail != null) {
                        int i = 0;
                        objRSemail.last();
                        int intNbrRow = objRSemail.getRow();
                        Url[] ContactEmails = new Url[intNbrRow];
                        objRSemail.beforeFirst();
                        while(objRSemail.next()) {
                            ContactEmails[i] = new Url();
                            ContactEmails[i].setId(objRSemail.getInt("url.id"));
                            ContactEmails[i].setDefaut(objRSemail.getInt("url.defaut"));
                            ContactEmails[i].setUrl(objRSemail.getString("url.url"));
                            ContactEmails[i].setType(objRSemail.getString("url.TYPE"));
                            i++;
                        }
                        contact.setEmails(ContactEmails);
                    }
                }
                objSecurite.getDataBaseManager().closeConnection();
            }
            return contact;
        }
        catch(SQLException sqlEx) {
            objLog.info("message 1 : "+sqlEx.getMessage());
            return null;
        }
        catch (Exception objx) {
            objLog.info("message 2: "+objx.getMessage());
            return null;
        }
    }
    
    /** Add/Create **/
    public Contact create(Contact objContact) {
        try {
            SecuriteManager objSecurite = new SecuriteManager(this.getThreadLocalRequest());
            if (new SecuriteManager(this.getThreadLocalRequest()).isAccessAllowed(Contact.class.getSimpleName(),"EXEC")) {
                String dateNOW = new DateManager().NOW();
                // USER

                /** Format Date here, GWT client side cannot !**/
                String strBirthDay = null;
                SimpleDateFormat sdfDDmmYYYY = new SimpleDateFormat(objSecurite.getProperties().getProperty("DATE_FORMAT_DATABASE"),Locale.US);
                if (objContact.getBirthDate() != null) {
                    strBirthDay = sdfDDmmYYYY.format(objContact.getBirthDate());
                }

                String sqlInsertContact = "INSERT INTO user SET firstname = ?, lastname = ?, birthdate = ?, description = ?, life = ?, created_date = ?, created_by = ?, modified_date = ?";
                String[] parameters = {objContact.getFirstName(),objContact.getLastName(),strBirthDay,objContact.getDescription(),objSecurite.getProperties().getProperty("ENABLE"),dateNOW,new Integer(objSecurite.getUser().getId()).toString(),dateNOW};
                objSecurite.getDataBaseManager().executeUpdate(sqlInsertContact, parameters);
                int userId = objSecurite.getDataBaseManager().getLastInsertedId();
                objContact.setId(userId);

                // CREATE ADDRESS
                if (objContact.getAddressHome() != null) {
                    String sqlInsertAddress = "INSERT INTO address (street,defaut,zipcodeId,created_date,created_by,TYPE) VALUES (?,?,?,?,?,?)";
                    String[] insertAddressParameter = {objContact.getAddressHome().getStreet(),objSecurite.getProperties().getProperty("DEFAULT"),new Integer(objContact.getAddressHome().getZipcodeId()).toString(),dateNOW,new Integer(objSecurite.getUser().getId()).toString(),objSecurite.getProperties().getProperty("TYPE_ADDRESS")};
                    objSecurite.getDataBaseManager().executeUpdate(sqlInsertAddress, insertAddressParameter);
                    int addressId = objSecurite.getDataBaseManager().getLastInsertedId();

                    // BIND ADDRESS TO user
                    String sqlInsertAdresseOrganisation = "INSERT INTO user_has_address (userId,addressId) VALUES (?,?)";
                    String[] insertAddressOrganisationParameter = {new Integer(objContact.getId()).toString(),new Integer(addressId).toString()};
                    objSecurite.getDataBaseManager().executeUpdate(sqlInsertAdresseOrganisation, insertAddressOrganisationParameter);
                }

                // PHONES
                if (objContact.getPhoneBusiness() != null) {
                    if (objContact.getPhoneBusiness().getAreacode() != null) {
                        if (objContact.getPhoneBusiness().getAreacode().length() > 1) {
                            int phonebusinessareacodeId = -1;
                            String sqlPhoneBusinessAreacode = "SELECT id FROM phone_areacode WHERE areacode = ?";
                            String[] selectPhoneBusinessAreacodeParameter = {objContact.getPhoneBusiness().getAreacode()};
                            ResultSet objRSpha = objSecurite.getDataBaseManager().executeQuery(sqlPhoneBusinessAreacode, selectPhoneBusinessAreacodeParameter);
                            if (objRSpha != null) {
                                if (objRSpha.next()) {
                                    phonebusinessareacodeId = objRSpha.getInt("id");
                                }
                            }
                            if (phonebusinessareacodeId < 1){
                                String sqlInsertPhoneBusinessAreacode = "INSERT INTO phone_areacode SET created_date = ?,modified_date = ?,life = 1,areacode = ?,internationalcode = 1,created_by = ?,modified_by = ?";
                                String[] insertPhoneBusinessAreacodeParameter = {dateNOW,dateNOW,objContact.getPhoneBusiness().getAreacode(),new Integer(objSecurite.getUser().getId()).toString(),new Integer(objSecurite.getUser().getId()).toString()};
                                objSecurite.getDataBaseManager().executeUpdate(sqlInsertPhoneBusinessAreacode, insertPhoneBusinessAreacodeParameter);
                                phonebusinessareacodeId = objSecurite.getDataBaseManager().getLastInsertedId();
                            }
                            String sqlInsertPhoneBusiness = "INSERT INTO phone (number,defaut,created_date,created_by,life,phoneAreacodeId,TYPE,extension) VALUES (?,?,?,?,?,?,?,?)";
                            String[] insertPhoneBusinessParameter = {objContact.getPhoneBusiness().getNumber(),objSecurite.getProperties().getProperty("DEFAULT"),dateNOW,new Integer(objSecurite.getUser().getId()).toString(),objSecurite.getProperties().getProperty("ENABLE"),new Integer(phonebusinessareacodeId).toString(),objSecurite.getProperties().getProperty("TYPE_PHONE_BUSINESS"),objContact.getPhoneBusiness().getExtension()};
                            objSecurite.getDataBaseManager().executeUpdate(sqlInsertPhoneBusiness, insertPhoneBusinessParameter);
                            int phonebusinessId = objSecurite.getDataBaseManager().getLastInsertedId();
                            // BIND PHONE TO ORGANISATION
                            String sqlInsertPhoneBusinessOrganisation = "INSERT INTO user_has_phone (userId,phoneId) VALUES (?,?)";
                            String[] insertPhoneBusinessOrganisationParameter = {new Integer(userId).toString(),new Integer(phonebusinessId).toString()};
                            objSecurite.getDataBaseManager().executeUpdate(sqlInsertPhoneBusinessOrganisation, insertPhoneBusinessOrganisationParameter);
                        }
                    }
                }

                if (objContact.getPhoneHome() != null) {
                    if (objContact.getPhoneHome().getAreacode() != null) {
                        if (objContact.getPhoneHome().getAreacode().length() > 1) {
                            int phoneHomeareacodeId = -1;
                            String sqlPhoneHomeAreacode = "SELECT id FROM phone_areacode WHERE areacode = ?";
                            String[] selectPhoneHomeAreacodeParameter = {objContact.getPhoneHome().getAreacode()};
                            ResultSet objRSpha = objSecurite.getDataBaseManager().executeQuery(sqlPhoneHomeAreacode, selectPhoneHomeAreacodeParameter);
                            if (objRSpha != null) {
                                if (objRSpha.next()) {
                                    phoneHomeareacodeId = objRSpha.getInt("id");
                                }
                            }
                            if (phoneHomeareacodeId < 1){
                                String sqlInsertPhoneHomeAreacode = "INSERT INTO phone_areacode SET created_date = ?,modified_date = ?,life = 1,areacode = ?,internationalcode = 1,created_by = ?,modified_by = ?";
                                String[] insertPhoneHomeAreacodeParameter = {dateNOW,dateNOW,objContact.getPhoneHome().getAreacode(),new Integer(objSecurite.getUser().getId()).toString(),new Integer(objSecurite.getUser().getId()).toString()};
                                objSecurite.getDataBaseManager().executeUpdate(sqlInsertPhoneHomeAreacode, insertPhoneHomeAreacodeParameter);
                                phoneHomeareacodeId = objSecurite.getDataBaseManager().getLastInsertedId();
                            }
                            String sqlInsertPhoneHome = "INSERT INTO phone (number,defaut,created_date,created_by,life,phoneAreacodeId,TYPE) VALUES (?,?,?,?,?,?,?)";
                            String[] insertPhoneHomeParameter = {objContact.getPhoneHome().getNumber(),objSecurite.getProperties().getProperty("DEFAULT"),dateNOW,new Integer(objSecurite.getUser().getId()).toString(),objSecurite.getProperties().getProperty("ENABLE"),new Integer(phoneHomeareacodeId).toString(),objSecurite.getProperties().getProperty("TYPE_PHONE_HOME")};
                            objSecurite.getDataBaseManager().executeUpdate(sqlInsertPhoneHome, insertPhoneHomeParameter);
                            int phoneHomeId = objSecurite.getDataBaseManager().getLastInsertedId();
                            // BIND PHONE TO ORGANISATION
                            String sqlInsertPhoneHomeOrganisation = "INSERT INTO user_has_phone (userId,phoneId) VALUES (?,?)";
                            String[] insertPhoneHomeOrganisationParameter = {new Integer(userId).toString(),new Integer(phoneHomeId).toString()};
                            objSecurite.getDataBaseManager().executeUpdate(sqlInsertPhoneHomeOrganisation, insertPhoneHomeOrganisationParameter);
                        }
                    }
                }

                if (objContact.getPhoneMobile() != null) {
                    if (objContact.getPhoneMobile().getAreacode() != null) {
                        if (objContact.getPhoneMobile().getAreacode().length() > 1) {
                            int phoneMobileareacodeId = -1;
                            String sqlPhoneMobileAreacode = "SELECT id FROM phone_areacode WHERE areacode = ?";
                            String[] selectPhoneMobileAreacodeParameter = {objContact.getPhoneMobile().getAreacode()};
                            ResultSet objRSpha = objSecurite.getDataBaseManager().executeQuery(sqlPhoneMobileAreacode, selectPhoneMobileAreacodeParameter);
                            if (objRSpha != null) {
                                if (objRSpha.next()) {
                                    phoneMobileareacodeId = objRSpha.getInt("id");
                                }
                            }
                            if (phoneMobileareacodeId < 1){
                                String sqlInsertPhoneMobileAreacode = "INSERT INTO phone_areacode SET created_date = ?,modified_date = ?,life = 1,areacode = ?,internationalcode = 1,created_by = ?,modified_by = ?";
                                String[] insertPhoneMobileAreacodeParameter = {dateNOW,dateNOW,objContact.getPhoneMobile().getAreacode(),new Integer(objSecurite.getUser().getId()).toString(),new Integer(objSecurite.getUser().getId()).toString()};
                                objSecurite.getDataBaseManager().executeUpdate(sqlInsertPhoneMobileAreacode, insertPhoneMobileAreacodeParameter);
                                phoneMobileareacodeId = objSecurite.getDataBaseManager().getLastInsertedId();
                            }
                            String sqlInsertPhoneMobile = "INSERT INTO phone (number,defaut,created_date,created_by,life,phoneAreacodeId,TYPE) VALUES (?,?,?,?,?,?,?)";
                            String[] insertPhoneMobileParameter = {objContact.getPhoneMobile().getNumber(),objSecurite.getProperties().getProperty("DEFAULT"),dateNOW,new Integer(objSecurite.getUser().getId()).toString(),objSecurite.getProperties().getProperty("ENABLE"),new Integer(phoneMobileareacodeId).toString(),objSecurite.getProperties().getProperty("TYPE_PHONE_MOBILE")};
                            objSecurite.getDataBaseManager().executeUpdate(sqlInsertPhoneMobile, insertPhoneMobileParameter);
                            int phoneMobileId = objSecurite.getDataBaseManager().getLastInsertedId();
                            // BIND PHONE TO ORGANISATION
                            String sqlInsertPhoneMobileOrganisation = "INSERT INTO user_has_phone (userId,phoneId) VALUES (?,?)";
                            String[] insertPhoneMobileOrganisationParameter = {new Integer(userId).toString(),new Integer(phoneMobileId).toString()};
                            objSecurite.getDataBaseManager().executeUpdate(sqlInsertPhoneMobileOrganisation, insertPhoneMobileOrganisationParameter);
                        }
                    }
                }

                // EMAIL
                if (objContact.getEmail() != null) {
                    if (objContact.getEmail().getUrl() != null) {
                        if (objContact.getEmail().getUrl().length() > 2) {
                            String sqlInsertUrl = "INSERT INTO url (url,defaut,life,created_date,created_by,TYPE) VALUES (?,?,?,?,?,?)";
                            String[] insertUrlParameter = {objContact.getEmail().getUrl(),objSecurite.getProperties().getProperty("DEFAULT"),objSecurite.getProperties().getProperty("ENABLE"),dateNOW,new Integer(objSecurite.getUser().getId()).toString(),objSecurite.getProperties().getProperty("TYPE_EMAIL")};
                            objSecurite.getDataBaseManager().executeUpdate(sqlInsertUrl, insertUrlParameter);
                            int urlId = objSecurite.getDataBaseManager().getLastInsertedId();
                            // BIND URL TO ORGANISATION
                            String sqlInsertUrlUser = "INSERT INTO user_has_url (userId,urlId) VALUES (?,?)";
                            String[] insertUrlUserParameter = {new Integer(userId).toString(),new Integer(urlId).toString()};
                            objSecurite.getDataBaseManager().executeUpdate(sqlInsertUrlUser, insertUrlUserParameter);
                        }
                    }
                }
                if (objContact.getEmail2() != null) {
                    if (objContact.getEmail2().getUrl() != null) {
                        if (objContact.getEmail2().getUrl().length() > 2) {
                            String sqlInsertUrl = "INSERT INTO url (url,defaut,life,created_date,created_by,TYPE) VALUES (?,?,?,?,?,?)";
                            String[] insertUrlParameter = {objContact.getEmail2().getUrl(),objSecurite.getProperties().getProperty("NOT_DEFAULT"),objSecurite.getProperties().getProperty("ENABLE"),dateNOW,new Integer(objSecurite.getUser().getId()).toString(),objSecurite.getProperties().getProperty("TYPE_EMAIL")};
                            objSecurite.getDataBaseManager().executeUpdate(sqlInsertUrl, insertUrlParameter);
                            int urlId = objSecurite.getDataBaseManager().getLastInsertedId();
                            // BIND URL TO ORGANISATION
                            String sqlInsertUrlOrganisation = "INSERT INTO user_has_url (userId,urlId) VALUES (?,?)";
                            String[] insertUrlOrganisationParameter = {new Integer(userId).toString(),new Integer(urlId).toString()};
                            objSecurite.getDataBaseManager().executeUpdate(sqlInsertUrlOrganisation, insertUrlOrganisationParameter);
                        }
                    }
                }

                // function/title/langue if tagkeyId < 1 no insert !!!!!!!!
                if (objContact.getFunction() != null) {
                    if (objContact.getFunction().getId() > 0) {
                        String sqlInsertContactFunction = "INSERT INTO user_key_function SET tagkeyId = ?, userId = ?";
                        String[] insertContactFunctionParameter = {new Integer(objContact.getFunction().getId()).toString(),new Integer(userId).toString()};
                        objSecurite.getDataBaseManager().executeUpdate(sqlInsertContactFunction,insertContactFunctionParameter);
                    }
                }
                if (objContact.getTitle() != null) {
                    if (objContact.getTitle().getId() > 0) {
                        String sqlInsertContactTitle = "INSERT INTO user_key_title SET tagkeyId = ?, userId = ?";
                        String[] insertContactTitleParameter = {new Integer(objContact.getTitle().getId()).toString(),new Integer(userId).toString()};
                        objSecurite.getDataBaseManager().executeUpdate(sqlInsertContactTitle,insertContactTitleParameter);
                    }
                }
                if (objContact.getLangue() != null) {
                    if (objContact.getLangue().getId() > 0) {
                        String sqlInsertContactLanguage = "INSERT INTO user_key_language SET tagkeyId = ?, userId = ?";
                        String[] insertContactLanguageParameter = {new Integer(objContact.getLangue().getId()).toString(),new Integer(userId).toString()};
                        objSecurite.getDataBaseManager().executeUpdate(sqlInsertContactLanguage,insertContactLanguageParameter);
                    }
                }
                /** IDENTITY : username + password if we have **/
                if ((objContact.getUsername() != null) && (objContact.getPassword() != null)) {
                    String sqlId = "INSERT INTO identity SET username = ?, password = ?, name = ?,life = ?,created_date = ?,date_modified_date = ?,created_by = ?,modified_by = ?";
                    String[] paramId = {objContact.getUsername(),objContact.getPassword(),objSecurite.getProperties().getProperty("IDENTITY_NAME_LOGIN"),objSecurite.getProperties().getProperty("ENABLE"),dateNOW,dateNOW,new Integer(objSecurite.getUser().getId()).toString(),new Integer(objSecurite.getUser().getId()).toString()};
                    objSecurite.getDataBaseManager().executeUpdate(sqlId,paramId);
                    int identityId = objSecurite.getDataBaseManager().getLastInsertedId();

                    String sqlBindIdentity = "INSERT INTO user_has_identity SET userId = ?,identityId = ?";
                    String[] paramBindIdentity = {new Integer(userId).toString(),new Integer(identityId).toString()};
                    objSecurite.getDataBaseManager().executeUpdate(sqlBindIdentity,paramBindIdentity);
                }

                // LINK CONTACT TO ORGANISATION
                String sqlInsertOrganisationHasContact = "INSERT INTO organisation_has_user SET organisationId = ?, userId = ?";
                String[] insertOrganisationContactParameter = {new Integer(objContact.getOrganisation().getId()).toString(),new Integer(objContact.getId()).toString()};
                objSecurite.getDataBaseManager().executeUpdate(sqlInsertOrganisationHasContact,insertOrganisationContactParameter);
                String sqlUserIsContact = "INSERT INTO user_contact SET userId = ?";
                String[] parameterUserIsContact = {new Integer(userId).toString()};
                objSecurite.getDataBaseManager().executeUpdate(sqlUserIsContact,parameterUserIsContact);

                objSecurite.getDataBaseManager().closeConnection();
            }
            return objContact;
        }
        catch (Exception objx) {
            objLog.info("message 2: "+objx);
            return null;
        }
    }

    /** Update/Modify **/
    public Contact modify(Contact objContact) {
        try {
            SecuriteManager objSecurite = new SecuriteManager(this.getThreadLocalRequest());
            if (new SecuriteManager(this.getThreadLocalRequest()).isAccessAllowed(Contact.class.getSimpleName(),"EXEC")) {
                String dateNOW = new DateManager().NOW();
                // UPDATE CONTACT(USER)

                /** Format Date here, GWT client side cannot !**/
                String strBirthDay = null;
                SimpleDateFormat sdfDDmmYYYY = new SimpleDateFormat(objSecurite.getProperties().getProperty("DATE_FORMAT_DATABASE"),Locale.US);
                if (objContact.getBirthDate() != null) {
                    strBirthDay = sdfDDmmYYYY.format(objContact.getBirthDate());
                }

                String sqlUpdateContact = "UPDATE user SET firstname = ?, lastname = ?, birthdate = ?, description = ?,modified_date = ?,modified_by = ? WHERE id = ?";
                String[] updateContactParameter = {objContact.getFirstName(),objContact.getLastName(),strBirthDay,objContact.getDescription(),dateNOW,new Integer(objSecurite.getUser().getId()).toString(),new Integer(objContact.getId()).toString()};
                objSecurite.getDataBaseManager().executeUpdate(sqlUpdateContact, updateContactParameter);

                // CREATE ADDRESS
                /** @WARNING : this would not work  if we add capacity for more than one address **/
                String sqlGetAddressId = "SELECT addressId FROM user_has_address WHERE userId = ?";
                String[] paramAddressId = {new Integer(objContact.getId()).toString()};
                int oldAddressId = -1;
                ResultSet rsAId = objSecurite.getDataBaseManager().executeQuery(sqlGetAddressId,paramAddressId);
                if (rsAId != null) {
                    if (rsAId.next()) {
                        oldAddressId = rsAId.getInt("addressId");
                    }
                }
                if (oldAddressId > 0) {
                    String sqlDeleteAId = "DELETE from address WHERE id = ?";
                    String[] paramDeleteAId = {rsAId.getString("addressId")};
                    objSecurite.getDataBaseManager().executeUpdate(sqlDeleteAId,paramDeleteAId);
                    String sqlDeleteAId2 = "DELETE from user_has_address WHERE userId = ? AND addressId = ?";
                    String[] paramDeleteAId2 = {new Integer(objContact.getId()).toString(),new Integer(oldAddressId).toString()};
                    objSecurite.getDataBaseManager().executeUpdate(sqlDeleteAId2,paramDeleteAId2);
                }

                if (objContact.getAddressHome() != null) {
                    String sqlInsertAddress = "INSERT INTO address (street,defaut,zipcodeId,created_date,created_by,TYPE) VALUES (?,?,?,?,?,?)";
                    String[] insertAddressParameter = {objContact.getAddressHome().getStreet(),objSecurite.getProperties().getProperty("DEFAULT"),new Integer(objContact.getAddressHome().getZipcodeId()).toString(),dateNOW,new Integer(objSecurite.getUser().getId()).toString(),objSecurite.getProperties().getProperty("TYPE_ADDRESS")};
                    objSecurite.getDataBaseManager().executeUpdate(sqlInsertAddress, insertAddressParameter);
                    int addressId = objSecurite.getDataBaseManager().getLastInsertedId();

                    // BIND ADDRESS TO user
                    String sqlInsertAdresseOrganisation = "INSERT INTO user_has_address (userId,addressId) VALUES (?,?)";
                    String[] insertAddressOrganisationParameter = {new Integer(objContact.getId()).toString(),new Integer(addressId).toString()};
                    objSecurite.getDataBaseManager().executeUpdate(sqlInsertAdresseOrganisation, insertAddressOrganisationParameter);
                }

                String sqlDeleteContactTitle = "DELETE FROM user_key_title WHERE userId = ?";
                String[] deleteContactTitleParameter = {new Integer(objContact.getId()).toString()};
                objSecurite.getDataBaseManager().executeUpdate(sqlDeleteContactTitle,deleteContactTitleParameter);
                String sqlInsertContactTitle = "INSERT INTO user_key_title SET tagkeyId = ?, userId = ?";
                String[] insertContactTitleParameter = {new Integer(objContact.getTitle().getId()).toString(),new Integer(objContact.getId()).toString()};
                objSecurite.getDataBaseManager().executeUpdate(sqlInsertContactTitle,insertContactTitleParameter);
                /**@TODO: if no Language SHOULD be an INSERT **/
                String sqlDeleteContactLangue = "DELETE FROM user_key_language WHERE userId = ?";
                String[] deleteContactLangueParameter = {new Integer(objContact.getId()).toString()};
                objSecurite.getDataBaseManager().executeUpdate(sqlDeleteContactLangue,deleteContactLangueParameter);
                String sqlInsertContactLanguage = "INSERT INTO user_key_language SET tagkeyId = ?, userId = ?";
                String[] insertContactLanguageParameter = {new Integer(objContact.getLangue().getId()).toString(),new Integer(objContact.getId()).toString()};
                objSecurite.getDataBaseManager().executeUpdate(sqlInsertContactLanguage,insertContactLanguageParameter);

                //IDENTITY
                String sqlIdD = "SELECT identityId FROM user_has_identity WHERE userId = ?";
                String[] paramIdD = {new Integer(objContact.getId()).toString()};
                ResultSet rsIdD = objSecurite.getDataBaseManager().executeQuery(sqlIdD,paramIdD);
                int identityId = 0;
                if (rsIdD != null) {
                    if (rsIdD.next()) {
                        identityId = rsIdD.getInt("identityId");
                    }
                }
                if (identityId > 0) {
                    String sqldeleteId = "DELETE FROM identity WHERE id = ?";
                    String[] paramdeleteId = {new Integer(identityId).toString()};
                    objSecurite.getDataBaseManager().executeUpdate(sqldeleteId,paramdeleteId);

                    String sqldeleteId2 = "DELETE FROM user_has_identity WHERE identityId = ?";
                    String[] paramdeleteId2 = {new Integer(identityId).toString()};
                    objSecurite.getDataBaseManager().executeUpdate(sqldeleteId2,paramdeleteId2);

                }

                if ((objContact.getUsername() != null) && (objContact.getPassword() != null)) {
                    String sqlId = "INSERT INTO identity SET username = ?, password = ?, name = ?,life = ?,created_date = ?,date_modified_date = ?,created_by = ?,modified_by = ?";
                    String[] paramId = {objContact.getUsername(),objContact.getPassword(),objSecurite.getProperties().getProperty("IDENTITY_NAME_LOGIN"),objSecurite.getProperties().getProperty("ENABLE"),dateNOW,dateNOW,new Integer(objSecurite.getUser().getId()).toString(),new Integer(objSecurite.getUser().getId()).toString()};
                    objSecurite.getDataBaseManager().executeUpdate(sqlId,paramId);
                    int identityId2 = objSecurite.getDataBaseManager().getLastInsertedId();

                    String sqlBindIdentity = "INSERT INTO user_has_identity SET userId = ?,identityId = ?";
                    String[] paramBindIdentity = {new Integer(objContact.getId()).toString(),new Integer(identityId2).toString()};
                    objSecurite.getDataBaseManager().executeUpdate(sqlBindIdentity,paramBindIdentity);
                }

                // PHONES ---------------------------------------------------------------------------------------------------------
                // FIRST DELETE
                String sqlGetPhoneId = "SELECT phoneId FROM user_has_phone WHERE userId = ?";
                String[] paramGetPhoneId = {new Integer(objContact.getId()).toString()};
                ResultSet rsGetPhoneId = objSecurite.getDataBaseManager().executeQuery(sqlGetPhoneId,paramGetPhoneId);
                String[] phonesId = null;
                if (rsGetPhoneId != null) {
                    int i = 0;
                    int intNbrRow = 0;
                    rsGetPhoneId.last();
                    intNbrRow = rsGetPhoneId.getRow();
                    phonesId = new String[intNbrRow];
                    rsGetPhoneId.beforeFirst();
                    while (rsGetPhoneId.next()) {
                        phonesId[i] = rsGetPhoneId.getString("phoneId");
                        i++;
                    }
                }
                if (phonesId != null) {
                    for (int i = 0; i < phonesId.length; i++) {
                        String sqldeletePhoneId = "DELETE FROM phone WHERE id = ?";
                        String[] paramdeletePhoneId = {phonesId[i]};
                        objSecurite.getDataBaseManager().executeUpdate(sqldeletePhoneId,paramdeletePhoneId);
                    }
                }
                // Create phone
                if (objContact.getPhoneBusiness() != null) {
                    if (objContact.getPhoneBusiness().getAreacode() != null) {
                        if (objContact.getPhoneBusiness().getAreacode().length() > 1) {
                            int phonebusinessareacodeId = -1;
                            String sqlPhoneBusinessAreacode = "SELECT id FROM phone_areacode WHERE areacode = ?";
                            String[] selectPhoneBusinessAreacodeParameter = {objContact.getPhoneBusiness().getAreacode()};
                            ResultSet objRSpha = objSecurite.getDataBaseManager().executeQuery(sqlPhoneBusinessAreacode, selectPhoneBusinessAreacodeParameter);
                            if (objRSpha != null) {
                                if (objRSpha.next()) {
                                    phonebusinessareacodeId = objRSpha.getInt("id");
                                }
                            }
                            if (phonebusinessareacodeId < 1){
                                String sqlInsertPhoneBusinessAreacode = "INSERT INTO phone_areacode SET created_date = ?,modified_date = ?,life = 1,areacode = ?,internationalcode = 1,created_by = ?,modified_by = ?";
                                String[] insertPhoneBusinessAreacodeParameter = {dateNOW,dateNOW,objContact.getPhoneBusiness().getAreacode(),new Integer(objSecurite.getUser().getId()).toString(),new Integer(objSecurite.getUser().getId()).toString()};
                                objSecurite.getDataBaseManager().executeUpdate(sqlInsertPhoneBusinessAreacode, insertPhoneBusinessAreacodeParameter);
                                phonebusinessareacodeId = objSecurite.getDataBaseManager().getLastInsertedId();
                            }
                            String sqlInsertPhoneBusiness = "INSERT INTO phone (number,defaut,created_date,created_by,life,phoneAreacodeId,TYPE,extension) VALUES (?,?,?,?,?,?,?,?)";
                            String[] insertPhoneBusinessParameter = {objContact.getPhoneBusiness().getNumber(),objSecurite.getProperties().getProperty("DEFAULT"),dateNOW,new Integer(objSecurite.getUser().getId()).toString(),objSecurite.getProperties().getProperty("ENABLE"),new Integer(phonebusinessareacodeId).toString(),objSecurite.getProperties().getProperty("TYPE_PHONE_BUSINESS"),objContact.getPhoneBusiness().getExtension()};
                            objSecurite.getDataBaseManager().executeUpdate(sqlInsertPhoneBusiness, insertPhoneBusinessParameter);
                            int phonebusinessId = objSecurite.getDataBaseManager().getLastInsertedId();
                            // BIND PHONE TO ORGANISATION
                            String sqlInsertPhoneBusinessOrganisation = "INSERT INTO user_has_phone (userId,phoneId) VALUES (?,?)";
                            String[] insertPhoneBusinessOrganisationParameter = {new Integer(objContact.getId()).toString(),new Integer(phonebusinessId).toString()};
                            objSecurite.getDataBaseManager().executeUpdate(sqlInsertPhoneBusinessOrganisation, insertPhoneBusinessOrganisationParameter);
                        }
                    }
                }

                if (objContact.getPhoneHome() != null) {
                    if (objContact.getPhoneHome().getAreacode() != null) {
                        if (objContact.getPhoneHome().getAreacode().length() > 1) {
                            int phoneHomeareacodeId = -1;
                            String sqlPhoneHomeAreacode = "SELECT id FROM phone_areacode WHERE areacode = ?";
                            String[] selectPhoneHomeAreacodeParameter = {objContact.getPhoneHome().getAreacode()};
                            ResultSet objRSpha = objSecurite.getDataBaseManager().executeQuery(sqlPhoneHomeAreacode, selectPhoneHomeAreacodeParameter);
                            if (objRSpha != null) {
                                if (objRSpha.next()) {
                                    phoneHomeareacodeId = objRSpha.getInt("id");
                                }
                            }
                            if (phoneHomeareacodeId < 1){
                                String sqlInsertPhoneHomeAreacode = "INSERT INTO phone_areacode SET created_date = ?,modified_date = ?,life = 1,areacode = ?,internationalcode = 1,created_by = ?,modified_by = ?";
                                String[] insertPhoneHomeAreacodeParameter = {dateNOW,dateNOW,objContact.getPhoneHome().getAreacode(),new Integer(objSecurite.getUser().getId()).toString(),new Integer(objSecurite.getUser().getId()).toString()};
                                objSecurite.getDataBaseManager().executeUpdate(sqlInsertPhoneHomeAreacode, insertPhoneHomeAreacodeParameter);
                                phoneHomeareacodeId = objSecurite.getDataBaseManager().getLastInsertedId();
                            }
                            String sqlInsertPhoneHome = "INSERT INTO phone (number,defaut,created_date,created_by,life,phoneAreacodeId,TYPE) VALUES (?,?,?,?,?,?,?)";
                            String[] insertPhoneHomeParameter = {objContact.getPhoneHome().getNumber(),objSecurite.getProperties().getProperty("DEFAULT"),dateNOW,new Integer(objSecurite.getUser().getId()).toString(),objSecurite.getProperties().getProperty("ENABLE"),new Integer(phoneHomeareacodeId).toString(),objSecurite.getProperties().getProperty("TYPE_PHONE_HOME")};
                            objSecurite.getDataBaseManager().executeUpdate(sqlInsertPhoneHome, insertPhoneHomeParameter);
                            int phoneHomeId = objSecurite.getDataBaseManager().getLastInsertedId();
                            // BIND PHONE TO ORGANISATION
                            String sqlInsertPhoneHomeOrganisation = "INSERT INTO user_has_phone (userId,phoneId) VALUES (?,?)";
                            String[] insertPhoneHomeOrganisationParameter = {new Integer(objContact.getId()).toString(),new Integer(phoneHomeId).toString()};
                            objSecurite.getDataBaseManager().executeUpdate(sqlInsertPhoneHomeOrganisation, insertPhoneHomeOrganisationParameter);
                        }
                    }
                }

                if (objContact.getPhoneMobile() != null) {
                    if (objContact.getPhoneMobile().getAreacode() != null) {
                        if (objContact.getPhoneMobile().getAreacode().length() > 1) {
                            int phoneMobileareacodeId = -1;
                            String sqlPhoneMobileAreacode = "SELECT id FROM phone_areacode WHERE areacode = ?";
                            String[] selectPhoneMobileAreacodeParameter = {objContact.getPhoneMobile().getAreacode()};
                            ResultSet objRSpha = objSecurite.getDataBaseManager().executeQuery(sqlPhoneMobileAreacode, selectPhoneMobileAreacodeParameter);
                            if (objRSpha != null) {
                                if (objRSpha.next()) {
                                    phoneMobileareacodeId = objRSpha.getInt("id");
                                }
                            }
                            if (phoneMobileareacodeId < 1){
                                String sqlInsertPhoneMobileAreacode = "INSERT INTO phone_areacode SET created_date = ?,modified_date = ?,life = 1,areacode = ?,internationalcode = 1,created_by = ?,modified_by = ?";
                                String[] insertPhoneMobileAreacodeParameter = {dateNOW,dateNOW,objContact.getPhoneMobile().getAreacode(),new Integer(objSecurite.getUser().getId()).toString(),new Integer(objSecurite.getUser().getId()).toString()};
                                objSecurite.getDataBaseManager().executeUpdate(sqlInsertPhoneMobileAreacode, insertPhoneMobileAreacodeParameter);
                                phoneMobileareacodeId = objSecurite.getDataBaseManager().getLastInsertedId();
                            }
                            String sqlInsertPhoneMobile = "INSERT INTO phone (number,defaut,created_date,created_by,life,phoneAreacodeId,TYPE) VALUES (?,?,?,?,?,?,?)";
                            String[] insertPhoneMobileParameter = {objContact.getPhoneMobile().getNumber(),objSecurite.getProperties().getProperty("DEFAULT"),dateNOW,new Integer(objSecurite.getUser().getId()).toString(),objSecurite.getProperties().getProperty("ENABLE"),new Integer(phoneMobileareacodeId).toString(),objSecurite.getProperties().getProperty("TYPE_PHONE_MOBILE")};
                            objSecurite.getDataBaseManager().executeUpdate(sqlInsertPhoneMobile, insertPhoneMobileParameter);
                            int phoneMobileId = objSecurite.getDataBaseManager().getLastInsertedId();
                            // BIND PHONE TO ORGANISATION
                            String sqlInsertPhoneMobileOrganisation = "INSERT INTO user_has_phone (userId,phoneId) VALUES (?,?)";
                            String[] insertPhoneMobileOrganisationParameter = {new Integer(objContact.getId()).toString(),new Integer(phoneMobileId).toString()};
                            objSecurite.getDataBaseManager().executeUpdate(sqlInsertPhoneMobileOrganisation, insertPhoneMobileOrganisationParameter);
                        }
                    }
                }

                // UPDATE EMAIL (2)
                // FIRST DELETE
                String sqlGetEmaileId = "SELECT urlId FROM user_has_url WHERE userId = ?";
                String[] paramGetEmailId = {new Integer(objContact.getId()).toString()};
                ResultSet rsGetEmailId = objSecurite.getDataBaseManager().executeQuery(sqlGetEmaileId,paramGetEmailId);
                String[] emailsId = null;
                if (rsGetEmailId != null) {
                    int i = 0;
                    int intNbrRow = 0;
                    rsGetEmailId.last();
                    intNbrRow = rsGetEmailId.getRow();
                    emailsId = new String[intNbrRow];
                    rsGetEmailId.beforeFirst();
                    while (rsGetEmailId.next()) {
                        emailsId[i] = rsGetEmailId.getString("urlId");
                        i++;
                    }
                }
                if (emailsId != null) {
                    for (int i = 0; i < emailsId.length; i++) {
                        String sqldeleteEmailId = "DELETE FROM url WHERE id = ?";
                        String[] paramdeleteEmailId = {emailsId[i]};
                        objSecurite.getDataBaseManager().executeUpdate(sqldeleteEmailId,paramdeleteEmailId);
                    }
                }

                if (objContact.getEmail() != null) {
                    if (objContact.getEmail().getUrl() != null) {
                        if (objContact.getEmail().getUrl().length() > 2) {
                            String sqlInsertUrl = "INSERT INTO url (url,defaut,life,created_date,created_by,TYPE) VALUES (?,?,?,?,?,?)";
                            String[] insertUrlParameter = {objContact.getEmail().getUrl(),objSecurite.getProperties().getProperty("DEFAULT"),objSecurite.getProperties().getProperty("ENABLE"),dateNOW,new Integer(objSecurite.getUser().getId()).toString(),objSecurite.getProperties().getProperty("TYPE_EMAIL")};
                            objSecurite.getDataBaseManager().executeUpdate(sqlInsertUrl, insertUrlParameter);
                            int urlId = objSecurite.getDataBaseManager().getLastInsertedId();
                            // BIND URL TO ORGANISATION
                            String sqlInsertUrlUser = "INSERT INTO user_has_url (userId,urlId) VALUES (?,?)";
                            String[] insertUrlUserParameter = {new Integer(objContact.getId()).toString(),new Integer(urlId).toString()};
                            objSecurite.getDataBaseManager().executeUpdate(sqlInsertUrlUser, insertUrlUserParameter);
                        }
                    }
                }
                if (objContact.getEmail2() != null) {
                    if (objContact.getEmail2().getUrl() != null) {
                        if (objContact.getEmail2().getUrl().length() > 2) {
                            String sqlInsertUrl = "INSERT INTO url (url,defaut,life,created_date,created_by,TYPE) VALUES (?,?,?,?,?,?)";
                            String[] insertUrlParameter = {objContact.getEmail2().getUrl(),objSecurite.getProperties().getProperty("NOT_DEFAULT"),objSecurite.getProperties().getProperty("ENABLE"),dateNOW,new Integer(objSecurite.getUser().getId()).toString(),objSecurite.getProperties().getProperty("TYPE_EMAIL")};
                            objSecurite.getDataBaseManager().executeUpdate(sqlInsertUrl, insertUrlParameter);
                            int urlId = objSecurite.getDataBaseManager().getLastInsertedId();
                            // BIND URL TO ORGANISATION
                            String sqlInsertUrlOrganisation = "INSERT INTO user_has_url (userId,urlId) VALUES (?,?)";
                            String[] insertUrlOrganisationParameter = {new Integer(objContact.getId()).toString(),new Integer(urlId).toString()};
                            objSecurite.getDataBaseManager().executeUpdate(sqlInsertUrlOrganisation, insertUrlOrganisationParameter);
                        }
                    }
                }

                // BIND CONTACT & ORGANISATION
                /**@TODO: If user has more than one organisation than this query sets all org to the same id !!!! **/
                String sqlUpdateOrganisationContact = "UPDATE organisation_has_user SET organisationId = ? WHERE userId = ?";
                String[] updateOrganisationContactParameter = {new Integer(objContact.getOrganisation().getId()).toString(),new Integer(objContact.getId()).toString()};
                objSecurite.getDataBaseManager().executeUpdate(sqlUpdateOrganisationContact, updateOrganisationContactParameter);

                objSecurite.getDataBaseManager().closeConnection();
            }
            return objContact;
        }
        catch (Exception objx) {
            objLog.info("message 2: "+objx);
            return null;
        }
    }

    /** Remove/Delete **/
    public int delete(String[] arrstrId) {
        try {
            return 1;
        }
        catch (Exception objx) {
            objLog.info("message 2: "+objx);
            return -1;
        }
    }

}